---
title: Dev Database
id: dev-database
slug: /concepts/dev-database
---

import Tabs from '@theme/Tabs';
import TabItem from '@theme/TabItem';

## Introduction

Some commands require a URL pointing to a _"Dev Database"_, typically a temporary and locally running database, which
Atlas uses to process and validate users' schemas, migrations and more. This requirement is necessary as Atlas cannot
replicate every database type 'X' in every version 'Y'.

To simplify the process of creating temporary databases for one-time use, Atlas can spin up an ephemeral local Docker
container using the special [docker driver](concepts/url.mdx), and clean it up at the end of the process. Here are a
few examples of how to use the docker driver:


<Tabs>
<TabItem value="mysql" label="MySQL" default>

```shell
# When working on a single database schema.
--dev-url "docker://mysql/8/dev"

# When working on multiple database schemas.
--dev-url "docker://mysql/8"
```

To work with a custom Docker image, use one of the following formats:

```shell
# When working on a single database schema.
docker+mysql://org/image/dev
docker+mysql://user/image:tag/dev
# For local/official images, leave host empty or use "_".
docker+mysql:///local/dev
docker+mysql://_/mariadb:latest/dev

# When working on multiple database schemas.
docker+mysql://local
docker+mysql://org/image
docker+mysql://user/image:tag
docker+mysql://_/mariadb:latest
```

</TabItem>
<TabItem value="mariadb" label="MariaDB">

```shell
# When working on a single database schema.
--dev-url "docker://maria/latest/schema"

# When working on multiple database schemas.
--dev-url "docker://maria/latest"
```

To work with a custom Docker image, use one of the following formats:

```shell
# When working on a single database schema.
docker+maria://org/image/dev
docker+maria://user/image:tag/dev
# For local/official images, leave host empty or use "_".
docker+maria:///local/dev
docker+maria://_/mysql:latest/dev

# When working on multiple database schemas.
docker+maria://local
docker+maria://org/image
docker+maria://user/image:tag
docker+maria://_/mariadb:latest
```

</TabItem>
<TabItem value="postgres" label="PostgreSQL">

```shell
# When working on a single database schema, use the auth-created
# "public" schema as the search path.
--dev-url "docker://postgres/15/dev?search_path=public"

# When working on multiple database schemas.
--dev-url "docker://postgres/15/dev"
```

To work with a custom Docker image, use one of the following formats:

```shell
# When working on a single database schema.
docker+postgres://org/image/dev?search_path=public
docker+postgres://ghcr.io/namespace/image:tag/dev?search_path=public
# For local/official images, leave host empty or use "_".
docker+postgres://_/local/dev?search_path=public
docker+postgres://_/official:latest/dev?search_path=public

# When working on multiple database schemas.
docker+postgres://org/image/dev
# Default database is "postgres".
docker+postgres://org/image:tag
```

</TabItem>
<TabItem value="sqlite" label="SQLite">

```shell
# Atlas supports working with an in-memory database in SQLite.
--dev-url "sqlite://dev?mode=memory"
```

</TabItem>
<TabItem value="sqlserver" label="SQL Server">

```shell
# When working on a single database schema.
--dev-url "docker://sqlserver/2022-latest/dev?mode=schema"

# When working on multiple database schemas.
--dev-url "docker://sqlserver/2022-latest/dev?mode=database"
```

To work with an Azure SQL Edge docker image or SQLServer version, use one of the following formats:

```shell
# Run SQLServer 2017-latest in schema mode.
docker://sqlserver/2017-latest?mode=schema
docker://sqlserver/2019-latest?mode=schema
docker://sqlserver/2022-latest?mode=schema

# Run Azure SQL Edge 1.0.7 in schema mode.
docker+sqlserver://mcr.microsoft.com/azure-sql-edge:1.0.7?mode=schema
```

</TabItem>
<TabItem value="clickhouse" label="ClickHouse">

```shell
# When working on a single database schema
--dev-url "docker://clickhouse/23.11/dev"

# When working on multiple database schemas.
--dev-url "docker://clickhouse/23.11"
```

To work with a custom Docker image, use one of the following formats:

```shell
# When working on a single database schema.
docker+clickhouse://org/image/dev
docker+clickhouse://user/image:tag/dev
# For local/official images, leave host empty or use "_".
docker+clickhouse:///local/dev
docker+clickhouse://_/clickhouse:latest/dev

# When working on multiple database schemas.
docker+clickhouse://local
docker+clickhouse://org/image
docker+clickhouse://user/image:tag
docker+clickhouse://_/clickhousedb:latest
```

</TabItem>
</Tabs>

## Baseline Schema

:::info LOGIN REQUIRED

The `docker` block is available for logged-in users only. To use this feature, run:

```
atlas login
```
:::

In some cases, there is a need to configure a baseline schema for the dev database so that every computation using the
dev-database starts from this baseline. For example, users' schemas or migrations rely on objects, extensions, or
other schema resources that are not managed by the project.

To configure such a baseline, use the `docker` block with the relevant image and pass to it the script for creating the
base schema for the project:

```hcl
docker "postgres" "dev" {
  image  = "postgres:15"
  schema = "public"
  // highlight-start
  baseline = <<SQL
   CREATE SCHEMA "auth";
   CREATE EXTENSION IF NOT EXISTS "uuid-ossp" SCHEMA "auth";
   CREATE TABLE "auth"."users" ("id" uuid NOT NULL DEFAULT auth.uuid_generate_v4(), PRIMARY KEY ("id"));
  SQL
  // highlight-end
}

env "local" {
  src = "file://schema.pg.hcl"
  dev = docker.postgres.dev.url
}
```

When managing multiple schemas or there is a need to generate migrations with table qualifiers, the `schema` argument
should not be set:

```hcl
docker "postgres" "dev" {
  image  = "postgres:15"
  // highlight-start
  // Use the file() function to read
  // the contents of the baseline script.
  baseline = file("baseline.sql")
  // highlight-end
}
```

MySQL and MariaDB users can use the `docker "mysql"` / `docker "mariadb"` to configure such block.


The code for the above examples is available in the [ariga/atlas-examples](https://github.com/ariga/atlas-examples) repository,
within the [migrate-diff-hcl](https://github.com/ariga/atlas-examples/tree/master/atlashcl/migrate-diff-hcl) and
[schema-apply-sql](https://github.com/ariga/atlas-examples/tree/master/atlashcl/schema-apply-sql) directories.

## Validation

The section below explains in more detail how Atlas uses the dev-database to process and validate users' schemas.

Suppose we want to the add the following `CHECK` constraint to the table below:

```hcl title="schema.hcl" {6-8}
table "t" {
  schema = schema.test
  column "c" {
    type = int
  }
  check "ck" {
    expr = "c <> d"
  }
}
```

After running [`schema apply`](reference.md#atlas-schema-apply), we get the following error because the `CHECK`
constraint is invalid, as column `d` does not exist.

```shell
atlas schema apply \
  --url "mysql://root:pass@:3308/test" \
  --to "schema.hcl"
```
```text
-- Planned Changes:
-- Modify "t" table
ALTER TABLE `test`.`t` ADD CONSTRAINT `ck` CHECK (c <> d), DROP COLUMN `c1`, ADD COLUMN `c` int NOT NULL
✔ Apply
Error: modify "t" table: Error 1054: Unknown column 'd' in 'check constraint ck expression'
exit status 1
```

Atlas cannot predict such errors without applying the schema file on the database, because some cases require parsing
and compiling SQL expressions, traverse their AST and validate them. This is already implemented by the database engine.

Migration failures can leave the database in a broken state. Some databases, like MySQL, do not support transactional
migrations due to [implicit COMMIT](https://dev.mysql.com/doc/refman/8.0/en/implicit-commit.html). However, this can be
avoided using the `--dev-url` option. Passing this to `schema apply` will first create and validate the desired state
(the HCL schema file) on temporary named-databases (schemas), and only then continue to `apply` the changes if it passed
successfully.

```shell
atlas schema apply \
  --url "mysql://root:pass@:3308/test" \
  --to "schema.hcl" \
  --dev-url "docker://mysql/8/test"
```
```text
Error: create "t" table: Error 3820: Check constraint 'ck' refers to non-existing column 'd'.
exit status 1
```

## Diffing

Atlas adopts the declarative approach for maintaining the schemas desired state, but provides two ways to manage and
apply changes on the database: `schema apply` and `migrate diff`. In both commands, Atlas compares the "current", and the
"desired" states and suggests a migration plan to migrate the "current" state to the "desired" state. For example, the
"current" state can be an inspected database or a migration directory, and the "desired" state can be an inspected
database, or an HCL file.

Schemas that are written in HCL files are defined in natural form by humans. However, databases store schemas in
normal form (also known as canonical form). Therefore, when Atlas compares two different forms it may suggest incorrect
or unnecessary schema changes, and using the `--dev-url` option can solve this (see the above section for more
in-depth example).

Let's see it in action, by adding an index-expression to our schema.

```hcl title="schema.hcl" {6-10}
table "t" {
  schema = schema.test
  column "c" {
    type = varchar(32)
  }
  index "i" {
    on {
      expr = "upper(concat('c', c))"
    }
  }
}
```

```shell
atlas schema apply \
  --url "mysql://root:pass@:3308/test" \
  --to "schema.hcl"
```
```text
-- Planned Changes:
-- Modify "t" table
ALTER TABLE `test`.`t` ADD INDEX `i` ((upper(concat('c', c))))
✔ Apply
```

We added a new index-expression to our schema, but using `schema inspect` will show our index in its normal form.

```shell
atlas schema inspect --url "mysql://root:pass@:3308/test"
```
```hcl {7-11}
table "t" {
  schema = schema.test
  column "c" {
    null = false
    type = varchar(32)
  }
  index "i" {
    on {
      expr = "upper(concat(_utf8mb4'c',`c`))"
    }
  }
}
```

Therefore, running `schema apply` again will suggest unnecessary schema changes.
```shell
atlas schema apply \
  --url "mysql://root:pass@:3308/test" \
  --to "schema.hcl"
```
```text
-- Planned Changes:
-- Modify "t" table
ALTER TABLE `test`.`t` DROP INDEX `i`
-- Modify "t" table
ALTER TABLE `test`.`t` ADD INDEX `i` ((upper(concat('c', c))))
✔ Abort
```

Similarly to the previous example, we will use the `--dev-url` option to solve this.

```shell
atlas schema apply \
  --url "mysql://root:pass@:3308/test" \
  --to "schema.hcl" \
  --dev-url "docker://mysql/8/test"
```
```text
Schema is synced, no changes to be made
```

Hooray! Our desired schema is synced and no changes have to be made.

:::info

Atlas cleans up after itself! You can use the same instance of a "Dev Database" for multiple environments, as long
as they are not accessed concurrently.

:::
